package org.boot.easyapi.util.jdbc.kingbase;

import lombok.extern.slf4j.Slf4j;
import org.boot.easyapi.pojo.db.TableDTO;
import org.boot.easyapi.util.CollUtil;
import org.boot.easyapi.util.ObjectUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
public class KingBaseAnalyzeUtil {
    /**
     * 获取指定表结构 - kingbase、postgresql
     * @param jdbcTemplate
     * @param table
     * @param finalSchema
     * @return
     */
    public static List<TableDTO> analyzeKingBaseES(JdbcTemplate jdbcTemplate, String table, String finalSchema) {
        List<Map<String, Object>> data = getKingBaseInfo(jdbcTemplate, table, finalSchema);
        if(CollUtil.isEmpty(data)) return null;
        List<TableDTO> result = new ArrayList<>();
        data.forEach(map -> {
            TableDTO dto = new TableDTO();
            dto.setName(ObjectUtil.isEmpty(map.get("field")) ? null : map.get("field").toString());
            dto.setType(ObjectUtil.isEmpty(map.get("type")) ? null : map.get("type").toString());
            dto.setNullable(ObjectUtil.isEmpty(map.get("notnull")) ? null : map.get("notnull").toString().equals("t"));
            dto.setComment(ObjectUtil.isEmpty(map.get("comment")) ? null : map.get("comment").toString());
            Integer size = ObjectUtil.isEmpty(map.get("length")) ? 0 : Integer.parseInt(map.get("length").toString());
            dto.setSize(size);
            result.add(dto);
        });
        return result;
    }

    private static List<Map<String, Object>> getKingBaseInfo(JdbcTemplate jdbcTemplate, String table, String finalSchema){
        //kingbase中不同模式下查询sql也不同
        String sql = null;
        try{
            log.info("KingbaseES查询表结构，尝试oracle模式查询");
            //尝试oracle模式查询
            sql = "SELECT a.attname AS field,\n" +
                    "       b.description AS comment,\n" +
                    "       t.typname AS type,\n" +
                    "       CASE WHEN a.atttypmod<=0 THEN NULL ELSE (a.atttypmod-4) END AS length,\n" +
                    "       a.attnotnull AS notnull\n" +
                    "  FROM sys_class c \n" +
                    "     INNER JOIN sys_namespace n on c.relnamespace = n.oid,\n" +
                    "     sys_attribute a LEFT JOIN sys_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" +
                    "     sys_type t    \n" +
                    " WHERE a.attnum > 0\n" +
                    "       and a.attrelid = c.oid\n" +
                    "       and a.atttypid = t.oid\n" +
                    "       AND nspname =" + "'" + finalSchema + "'" +
                    "       AND c.relname =" + "'" + table + "'" +
                    " ORDER BY a.attnum;";
            return jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            log.error("KingbaseES查询oracle模式表结构失败", e);
            log.info("KingbaseES查询表结构，尝试pg模式查询");
            //尝试pg模式查询
            sql = "SELECT a.attname AS field,\n" +
                    "       d.description AS comment,\n" +
                    "       pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,\n" +
                    "       CASE WHEN a.atttypmod > 0 THEN a.atttypmod - 4 ELSE NULL END AS length,\n" +
                    "       a.attnotnull AS notnull\n" +
                    "FROM pg_class c\n" +
                    "JOIN pg_namespace n ON c.relnamespace = n.oid\n" +
                    "JOIN pg_attribute a ON a.attrelid = c.oid\n" +
                    "LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = a.attnum\n" +
                    "WHERE n.nspname = '" +  finalSchema +"'\n" +
                    "  AND c.relname = '" + table + "'\n" +
                    "  AND a.attnum > 0\n" +
                    "  AND a.attisdropped = false\n" +
                    "ORDER BY a.attnum;";
            return jdbcTemplate.queryForList(sql);
        }
    }
}

